﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spSecurity_GrantAccess]
#-- Purpose:		Grants a user an access right.  If necessary, it will create
#--					the login, database user, and connect rights in addition
#--					to the requested right.
#--	Last Update:	04/16/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spSecurity_GrantAccess]
(
	@role_name		sysname,
	@user_name		sysname,
	@is_role		bit				= 1
)
AS

SET NOCOUNT ON

BEGIN TRY

	DECLARE	@msg nvarchar(4000), @sql varchar(MAX), @progname sysname

	--- Define the name of this program (for logs)
	SELECT		@progname = 'spSecurity_GrantAccess'

	SELECT	@msg = '[spSecurity_GrantAccess] Started -  User: ' + @user_name + ', Role: ' + @role_name
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @msg

	--- Create the login
	IF NOT EXISTS(SELECT TOP 1 name FROM [sys].[server_principals] WHERE name = @user_name)
	  BEGIN
	
		IF CHARINDEX(@user_name, '\') > 0
			SET @sql = 'CREATE LOGIN [' + @user_name + '] FROM WINDOWS;'
		ELSE
		  BEGIN
			DECLARE @password varchar(128)
			EXECUTE [dbo].[uspGeneratePassword] 20, 1, 1, 1, 1, @password OUTPUT
			SET @sql = 'CREATE LOGIN [' + @user_name + '] WITH PASSWORD=''' + @password + ''' MUST_CHANGE;'
		  END

		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sql
		EXEC	(@sql)

	  END
	ELSE IF EXISTS (SELECT TOP 1 name FROM [sys].[server_principals] WHERE name = @user_name AND is_disabled = 1)
	  BEGIN
		SET @sql = 'ALTER LOGIN [' + @user_name + '] ENABLE;'
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sql
		EXEC	(@sql)
	  END

	--- Create the user
	IF NOT EXISTS(SELECT TOP 1 name FROM [sys].[sysusers] WHERE name = @user_name)
	  BEGIN

		SET @sql = 'CREATE USER [' + @user_name + '] FOR LOGIN ['+ @user_name + '];'

		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sql
		EXEC	(@sql)

	  END

	--- Ensure CONNECT has been granted  
		SET @sql = 'GRANT CONNECT TO ['+ @user_name + '];'

		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sql
		EXEC	(@sql)


	--- Grant the requested role
	IF @is_role = 1
		SET @sql = 'EXEC [sp_addrolemember] @rolename = N''' + @role_name + ''', @membername = N''' + @user_name + ''';'
	ELSE
		SET @sql = 'GRANT ' + @role_name + ' TO ['+ @user_name + '];'

	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sql
	EXEC	(@sql)



	SELECT	@msg = '[spSecurity_RevokeAccess] Completed - User: ' + @user_name + ', Role: ' + @role_name
	EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @msg
END TRY

BEGIN CATCH

	PRINT 'ERROR: ' + ERROR_MESSAGE()

END CATCH

SET NOCOUNT OFF
